Relational database systems store data in tables organized by columns and rows. The tables are typically linked together by “relationships” that simplify the storage of data and make complex queries against the database more efficient. Structured Query Language (or SQL) is a standardized language for creating and operating on relational databases.
A relational database system typically includes an “optimizer” that plans the execution of SQL queries. For example, if a query requires access to a table, the optimizer will select an “access path” which either produces the requested results in the shortest period of time or satisfies some other criteria.
In some cases, tables in a relational database system may contain a very large amount of data. For example, many large retail chains may operate relational databases that contain daily sales figures. The tables of daily sales figures may include millions or billions of rows and a large number of columns. A better access path is important in such cases because scanning all rows and/or columns in a is large table is time consuming and may impose an unacceptable load on computing resources.
Typically, a database administrator defines an “index” that contains one or more frequently accessed columns on a table. An index is a smaller table which references columns in another table. Accessing a table through an index can avoid the need to perform an all-row scan on the table. However, to use an index for a single table access, an index key that contains one constant value per index column needs to be specified in the query. Otherwise, the optimizer cannot use the index to access the table and will revert to using an all-row scan operator.